--region [dbo].[AddAction]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddAction]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddAction]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddAction]
GO

CREATE PROCEDURE [dbo].[AddAction]
	@FromUserId int,
	@FromUserName nvarchar(50),
	@ToUserId int,
	@ToUserName nvarchar(50),
	@ActionType int,
	@Remark nvarchar(2000),
	@PostTime datetime,
	@IsRead int
AS

INSERT INTO [Action] (
	[FromUserId],
	[FromUserName],
	[ToUserId],
	[ToUserName],
	[ActionType],
	[Remark],
	[PostTime],
	[IsRead]
) VALUES (
	@FromUserId,
	@FromUserName,
	@ToUserId,
	@ToUserName,
	@ActionType,
	@Remark,
	@PostTime,
	@IsRead
)

GO
--endregion


--region [dbo].[UpdateAction]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateAction]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateAction]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateAction]
GO

CREATE PROCEDURE [dbo].[UpdateAction]
	@Id int,
	@FromUserId int,
	@FromUserName nvarchar(50),
	@ToUserId int,
	@ToUserName nvarchar(50),
	@ActionType int,
	@Remark nvarchar(2000),
	@PostTime datetime
AS

UPDATE [Action] SET
	[FromUserId] = @FromUserId,
	[FromUserName] = @FromUserName,
	[ToUserId] = @ToUserId,
	[ToUserName] = @ToUserName,
	[ActionType] = @ActionType,
	[Remark] = @Remark,
	[PostTime] = @PostTime
WHERE
	[Id] = @Id

GO
--endregion

--region [dbo].[UpdateActionState]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateActionState]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateActionState]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateActionState]
GO

CREATE PROCEDURE [dbo].[UpdateActionState]
	@uid int,
	@state int
AS

UPDATE [Action] SET
	[IsRead] = @state
WHERE
	[ToUserId] = @uid

GO
--endregion


--region [dbo].[DelAction]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelAction]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelAction]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelAction]
GO

CREATE PROCEDURE [dbo].[DelAction]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[Action]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetAction]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetAction]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetAction]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetAction]
GO

CREATE PROCEDURE [dbo].[GetAction]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[FromUserId],
	[FromUserName],
	[ToUserId],
	[ToUserName],
	[ActionType],
	[Remark],
	[PostTime]
FROM
	[Action]
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[GetActionList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetActionList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetActionList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetActionList]
GO

CREATE PROCEDURE [dbo].[GetActionList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [Action]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT
	[Id],
	[FromUserId],
	[FromUserName],
	[ToUserId],
	[ToUserName],
	[ActionType],
	[Remark],
	[PostTime],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[Action]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetActionListToUser]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetActionListToUser]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetActionListToUser]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetActionListToUser]
GO

CREATE PROCEDURE [dbo].[GetActionListToUser]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [Action]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT
	[Action].[Id],
	[FromUserId],
	[FromUserName],
	[ToUserId],
	[ActionType],
	[Remark],
	[PostTime],
	[IsRead],
	''FromUserAvatar'' = [UserInfo].[Avatar],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[Action], [UserInfo]
WHERE
	[Action].[FromUserId] = [UserInfo].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO

--endregion

